package com.turing.post.organization.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.organization.util.MerchantFeeSettlementPolicy;
import com.turing.post.organization.util.MerchantFeeSettlementPolicyDao;
import com.turing.post.organization.util.OrganApplicationException;
import com.turing.post.organization.util.OrganRespCode;
import com.turing.post.report.util.MerchantDaySettlementDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * @author zxp
 * @Jan 3, 2013
 */

@Stateless(mappedName = "organization.impl.MerchantFeeSettlementPolicyDaoImpl")
@Remote
@Local
public class MerchantFeeSettlementPolicyDaoImpl implements
		MerchantFeeSettlementPolicyDao {
	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	@EJB(mappedName = "report.impl.MerchantDaySettlementDaoImpl")
	private MerchantDaySettlementDao MerchantDaySettlementDaoImpl;

	@Override
	public void deleteMerchantFeeSettlementPolicyByFeePolicyId(
			BigInteger feePolicyId) throws PCPSApplicationException {
		String sql = "delete from MerchantFeeSettlementPolicy where FeePolicyId=?1";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, feePolicyId);
		try {
			query.executeUpdate();
		} catch (Exception e) {
			throw new OrganApplicationException(
					OrganApplicationException.ERRCODE_CATEGORY_APPLICATION,
					OrganRespCode.ISSUE_INSERT_UPDATE, "删除商户手续费收入结算策略失败，已被引用!");
		}
	}

	@Override
	public MerchantFeeSettlementPolicy getMerchantFeeSettlementPolicyByFeePolicyId(
			BigInteger feePolicyId) {
		return _em.find(MerchantFeeSettlementPolicy.class, feePolicyId);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantFeeSettlementPolicy> getMerchantFeeSettlementPolicyDropdownList(
			PCPSWebInput webInput) {
		String branchNo = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select * from MerchantFeeSettlementPolicy c where c.MerchantNo like '"
				+ branchNo + "%' order by c.FeePolicyId";
		Query query = _em.createNativeQuery(sql,
				MerchantFeeSettlementPolicy.class);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantFeeSettlementPolicy> getMerchantFeeSettlementPolicyList(
			PCPSWebInput webInput, String merchantNo, String status,
			String policy, Integer page) {
		String sql = "select * from MerchantFeeSettlementPolicy c where 1=1";
		// 平台机构管理员归属机构号为空
		if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
			sql = sql + "  and c.MerchantNo like '" + issueCompanyNo + "%'";
		}
		if (!PCPSUtil.isEmpty(merchantNo)) {
			sql = sql + "  and c.MerchantNo like '%" + merchantNo + "%'";
		}
		if (!PCPSUtil.isEmpty(status)) {
			sql = sql + "  and c.Status = '" + status + "'";
		}
		if (!PCPSUtil.isEmpty(policy)) {
			sql = sql + "  and c.Policy = '" + policy + "'";
		}
		sql = sql + " order by c.FeePolicyId";
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		Query query = _em.createNativeQuery(sql,
				MerchantFeeSettlementPolicy.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		return query.getResultList();
	}

	@Override
	public BigInteger getMerchantFeeSettlementPolicyTotal(
			PCPSWebInput webInput, String merchantNo, String status,
			String policy) {
		String sql = "select nvl(count(*),0) from MerchantFeeSettlementPolicy c where 1=1";
		// 平台机构管理员归属机构号为空
		if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
			sql = sql + "  and c.MerchantNo like '" + issueCompanyNo + "%'";
		}
		if (!PCPSUtil.isEmpty(merchantNo)) {
			sql = sql + "  and c.MerchantNo like '%" + merchantNo + "%'";
		}
		if (!PCPSUtil.isEmpty(status)) {
			sql = sql + "  and c.Status = '" + status + "'";
		}
		if (!PCPSUtil.isEmpty(policy)) {
			sql = sql + "  and c.Policy = '" + policy + "'";
		}
		Query query = _em.createNativeQuery(sql);
		return new BigInteger(query.getSingleResult().toString());
	}

	@Override
	public void insertMerchantFeeSettlementPolicy(
			MerchantFeeSettlementPolicy merchantFeeSettlementPolicy)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, merchantFeeSettlementPolicy);
	}

	@Override
	public void updateMerchantFeeSettlementPolicy(
			MerchantFeeSettlementPolicy merchantFeeSettlementPolicy)
			throws PCPSApplicationException {
		GeneralDao.update(_em, merchantFeeSettlementPolicy);
	}

	/**
	 * 根据商户号，trandate取得结算策略的集合
	 * 
	 * @param merchantNo
	 * @param trandate
	 * @return MerchantFeeSettlementPolicy集合
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantFeeSettlementPolicy> getMerchantFeeSettlementPolicyListByMerchantnoAndTrandate(
			String merchantNo, Date trandate) {
		String sql = "select * from MerchantFeeSettlementPolicy t where 1=1 "
				+ "and ?1 between t.policystartdate and t.policyenddate "
				+ "and status='1' and merchantno=?2 " + "order by LimitAmount";
		Query query = _em.createNativeQuery(sql,
				MerchantFeeSettlementPolicy.class);
		query.setParameter(1, trandate);
		query.setParameter(2, merchantNo);
		return query.getResultList();
	}

	/**
	 * 根据商户号，trandate取得所有达到阀值的结算策略的集合
	 * 
	 * @param merchantNo
	 * @param trandate
	 * @return MerchantFeeSettlementPolicy集合
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantFeeSettlementPolicy> getEffectMerchantFeeSettlementPolicyList(
			String merchantNo, Date trandate) {
		String sql = "select * from MerchantFeeSettlementPolicy t where 1=1 "
				+ "and ?1 between t.policystartdate and t.policyenddate "
				+ "and status='1' " + "and merchantno=?2 "
				+ "order by LimitAmount";
		Query query = _em.createNativeQuery(sql,
				MerchantFeeSettlementPolicy.class);
		query.setParameter(1, trandate);
		query.setParameter(2, merchantNo);
		List<MerchantFeeSettlementPolicy> list = query.getResultList();
		List<MerchantFeeSettlementPolicy> returnlist = new ArrayList<MerchantFeeSettlementPolicy>();
		MerchantFeeSettlementPolicy mfs = null;

		// 将得到的结算比率集合做处理，当结算方式是阶梯追溯和差额无追溯时，只返回达到阀值的结算比率。其他情况将所有结算比率集合返回。
		for (int i = 0; i < list.size(); i++) {
			mfs = list.get(i);
			if (mfs.getLimitAmount().longValue() <= MerchantDaySettlementDaoImpl
					.getLimitAmountByDate(merchantNo, mfs.getPolicyStartDate(),
							trandate)
					|| (!mfs
							.getStatus()
							.equals(
									MerchantFeeSettlementPolicy.POLICY_LADDERRETROSPECTIVE) && !mfs
							.getStatus()
							.equals(
									MerchantFeeSettlementPolicy.POLICY_NORETROSPECTIVE))) {
				returnlist.add(mfs);
				PCPSUtil.Log("mfs===" + mfs.getFeeRatio());
			}
		}
		return returnlist;
	}

	/**
	 * 根据商户号，起止时间段取得结算策略的集合
	 * 
	 * @param merchantNo
	 * @param trandate
	 * @return MerchantFeeSettlementPolicy集合
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantFeeSettlementPolicy> getMerchantFeeSettlementPolicyListByMerchantnoAndTimeQuantum(
			String merchantNo, Date startdate, Date enddate) {
		String sql = "select * from MerchantFeeSettlementPolicy t where 1=1 "
				+ " and ( ?1 between t.policystartdate and t.policyenddate or ?2 between t.policystartdate and t.policyenddate ) "
				+ " and status=?4 and merchantno=?3 order by LimitAmount";
		Query query = _em.createNativeQuery(sql,
				MerchantFeeSettlementPolicy.class);
		query.setParameter(1, startdate);
		query.setParameter(2, enddate);
		query.setParameter(3, merchantNo);
		query.setParameter(4, MerchantFeeSettlementPolicy.STATUS_APPROVE);
		return query.getResultList();
	}

	/**
	 * 判断是否存在指定商户指定日期且已批准的结算策略
	 * 
	 * @param merchantNo
	 * @param tranDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public int getSettlementPolicyCategory(String merchantNo, Date tranDate) {
		String sql = "select nvl(sum(case when t.Policy = ?4 then 1 else 0 end),0), nvl(sum(case when t.Policy <> ?4 then 1 else 0 end),0) from MerchantFeeSettlementPolicy t where 1=1 "
				+ "and t.merchantno = ?1 and (?2 >= t.policystartdate) "
				+ "and (?2 <= t.policyenddate or t.policyenddate is null) "
				+ "and t.status=?3 ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranDate);
		query.setParameter(3, MerchantFeeSettlementPolicy.STATUS_APPROVE);
		query
				.setParameter(4,
						MerchantFeeSettlementPolicy.POLICY_BYBUSINESSTYPE);
		List<Object[]> mfsplist = query.getResultList();
		if (mfsplist == null || mfsplist.size() != 1)
			return (0);
		Object[] obj = mfsplist.get(0);
		int ret = 0;
		int hval = Integer.valueOf(obj[0].toString());
		int nval = Integer.valueOf(obj[1].toString());
		if (hval > 0)
			ret |= 0x80;
		if (nval > 0)
			ret |= 0x01;
		return ret;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantFeeSettlementPolicy> getMFSPByMrchNoAndDateInclude(
			String merchantNo, Date startDate, Date endDate, String policy,
			String businessType, BigInteger limitAmount) {
		String sql = "select * from MerchantFeeSettlementPolicy t "
				+ "where t.merchantno = ?1 and t.status in ('0','1') "
				+ "and ( "
				// A< a<=b <B
				+ "(to_date(?2,'yyyy-MM-dd') > t.policystartdate and to_date(?3,'yyyy-MM-dd') < t.policyenddate) "
				+ "or "
				// a<=b =A
				+ "(to_date(?2,'yyyy-MM-dd') < t.policystartdate and to_date(?3,'yyyy-MM-dd') = t.policystartdate) "
				+ "or "
				// A= a<=b <>B
				+ "(to_date(?2,'yyyy-MM-dd') = t.policystartdate and to_date(?3,'yyyy-MM-dd') <> t.policyenddate) "
				+ "or "
				// A<> a<=b =B
				+ "(to_date(?2,'yyyy-MM-dd') <> t.policystartdate and to_date(?3,'yyyy-MM-dd') = t.policyenddate) "
				+ "or "
				// B= a<=b
				+ "(to_date(?2,'yyyy-MM-dd') = t.policyenddate and to_date(?3,'yyyy-MM-dd') > t.policyenddate) "
				+ "or "
				// a < A<=B < b
				+ "(to_date(?2,'yyyy-MM-dd') < t.policystartdate and to_date(?3,'yyyy-MM-dd') > t.policyenddate) "
				+ ") and t.policy = ?4 "
				+ "and (t.businesstype=?5 or ?5 is null) "
				+ "and (t.limitamount = ?6 or ?6 is null)";
		Query query = _em.createNativeQuery(sql,
				MerchantFeeSettlementPolicy.class);

		query.setParameter(1, merchantNo);
		query.setParameter(2, PCPSUtil.getStringYMDByDate(startDate));
		query.setParameter(3, PCPSUtil.getStringYMDByDate(endDate));
		query.setParameter(4, policy);
		query.setParameter(5, businessType);
		query.setParameter(6, limitAmount);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantFeeSettlementPolicy> getMFSPByMrchNoAndDateOverlapping(
			String merchantNo, Date startDate, Date endDate, String policy,
			String businessType, BigInteger limitAmount) {
		String sql = "select * from MerchantFeeSettlementPolicy t "
				+ "where t.merchantno = ?1 "
				+ "and t.policystartdate = to_date(?2,'yyyy-MM-dd') "
				+ "and t.policyenddate = to_date(?3,'yyyy-MM-dd') "
				+ "and t.policy = ?4 and t.status in ('0','1') "
				+ "and (t.businesstype=?5 or ?5 is null)"
				+ "and (t.limitamount = ?6 or ?6 is null)";
		Query query = _em.createNativeQuery(sql,
				MerchantFeeSettlementPolicy.class);

		query.setParameter(1, merchantNo);
		query.setParameter(2, PCPSUtil.getStringYMDByDate(startDate));
		query.setParameter(3, PCPSUtil.getStringYMDByDate(endDate));
		query.setParameter(4, policy);
		query.setParameter(5, businessType);
		query.setParameter(6, limitAmount);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantFeeSettlementPolicy> getMFSPListForContract(
			PCPSWebInput webInput, String merchantNo, String contractId,
			Integer page) {
		// 平台机构管理员归属机构号为空
		if (PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return null;
		}
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		String sql = "select * from MerchantFeeSettlementPolicy c,Merchant m "
				+ "where c.merchantno = ?1 and c.contractid = ?2 "
				+ "and c.merchantno = m.merchantno "
				+ "and m.issuecompanyno = ?3 order by c.FeePolicyId";

		Query query = _em.createNativeQuery(sql,
				MerchantFeeSettlementPolicy.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		query.setParameter(1, merchantNo);
		query.setParameter(2, contractId);
		query.setParameter(3, PCPSUtil.getIssueCompanyNo(webInput));
		return query.getResultList();
	}

	@Override
	public BigInteger getMFSPTotalForContract(PCPSWebInput webInput,
			String merchantNo, String contractId) {
		// 平台机构管理员归属机构号为空
		if (PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return null;
		}
		String sql = "select nvl(count(*),0) from MerchantFeeSettlementPolicy c,Merchant m "
				+ "where c.merchantno = ?1 and c.contractid = ?2 "
				+ "and c.merchantno = m.merchantno "
				+ "and m.issuecompanyno = ?3";

		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, contractId);
		query.setParameter(3, PCPSUtil.getIssueCompanyNo(webInput));
		return new BigInteger(query.getSingleResult().toString());
	}

	@Override
	public void removeMFSPTotalForContract(PCPSWebInput webInput,
			String merchantNo, String contractId)
			throws PCPSApplicationException {
		// 平台机构管理员归属机构号为空
		if (PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return;
		}
		String sql = "delete from MerchantFeeSettlementPolicy c "
				+ "where c.merchantno = ?1 and c.contractid = ?2 ";

		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, contractId);
		try {
			query.executeUpdate();
		} catch (Exception e) {
			throw new OrganApplicationException(
					OrganApplicationException.ERRCODE_CATEGORY_APPLICATION,
					OrganRespCode.ISSUE_INSERT_UPDATE, "删除商户手续费收入结算策略失败，已被引用!");
		}
	}

	@Override
	public void updateMFSPByContractId(BigInteger contractId,
			MerchantFeeSettlementPolicy mfsp) throws PCPSApplicationException {
		String sql = "update merchantfeesettlementpolicy t"
				+ " set t.status=?2,t.policystartdate=?3,"
				+ " t.policyenddate=?4,t.lastmodifytime=?5,t.policy=?6"
				+ " where t.contractid = ?1";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, contractId);
		query.setParameter(2, mfsp.getStatus());
		query.setParameter(3, mfsp.getPolicyStartDate());
		query.setParameter(4, mfsp.getPolicyEndDate());
		query.setParameter(5, mfsp.getLastModifyTime());
		query.setParameter(6, mfsp.getPolicy());
		query.executeUpdate();
	}

	@Override
	public void modifyMFSPEndDateByContractId(BigInteger extensionID,
			BigInteger extensionMonth) throws PCPSApplicationException {
		String sql = "UPDATE MERCHANTFEESETTLEMENTPOLICY SET LASTMODIFYTIME = sysdate, POLICYENDDATE = ADD_MONTHS(POLICYENDDATE, ?1) WHERE  CONTRACTID  = ?2";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, extensionMonth);
		query.setParameter(2, extensionID);
		query.executeUpdate();
	}

	@Override
	public Integer checkMFSPTotalForContract(String merchantNo,
			String contractId, String policy) {
		String sql = "select nvl(count(*),0) from MerchantFeeSettlementPolicy c,Merchant m "
				+ " where c.merchantno = ?1 and c.contractid = ?2"
				+ " and c.merchantno = m.merchantno"
				+ " and (?3 is null or c.policy = ?3)";

		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);
		query.setParameter(2, contractId);
		query.setParameter(3, policy);
		return new Integer(query.getSingleResult().toString());
	}

}